import pandas as pd
import pymysql

pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 1000)

def insert_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('successful')
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()

def select_mysql(sqll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqll)
        result = cursor.fetchall()[0][0]
        print(result)
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()
    return result

df = pd.read_excel('示例表格(1).xlsx', index_col=0, header=[0, 1, 2])
df = df.fillna(0)
b = df.columns.values
for row in df.index.values:
    data = df.loc[row].values
    print(data)
    print(len(data))
    for i in range(7, len(data)):
        name = data[6]
        site = b[i][0]
        time = b[i][1]
        type = b[i][2]
        value = data[i]
        name_id = select_mysql(sqll='select name from side_table where des = "%s"' % (name))
        print(name_id)
        site_id = select_mysql(sqll='select name from side_table where des = "%s"' % (site))
        print(site_id)
        print(name, name_id, site, site_id, time, type, value)
        print('----------')
        if type == '定性':
            print('dx')
            insert_mysql(
                sql='insert into main_table(project,yearid,name,site,month,qualitative,quantitative)values("%s","%s","%s","%s","%s","%s","%s")' % (
                "river", "2022", name_id, site_id, time, value, "0"))
        else:
            print('dl')
            insert_mysql(
                sql='insert into main_table(project,yearid,name,site,month,qualitative,quantitative)values("%s","%s","%s","%s","%s","%s","%s")' % (
                "river_01", "2022", name_id, site_id, time, "0", value))

